--
-- Description: Script to get the number of days to pull data for.
-- The number of days are the difference between the max date in the
-- EventHits table and the current date.
-- Also truncate the StageEventHits table.
-- Date created: 6 May 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the stored procedure if it exists and recreate it.
--
if exists
(
	select * 
	from sys.objects
	where object_id = object_id(N'[getEventHitDays_sp]')
	and type in (N'P', N'PC')
)
begin
    drop procedure [dbo].[getEventHitDays_sp]
end
go

create procedure [dbo].[getEventHitDays_sp]
(
	@EventID bigint
	, @EventTypeName nvarchar(50)
)
as
begin
	declare @EventTypeID smallint
			, @MaxDateModified smalldatetime
			, @NumberOfDays smallint

	--
	-- initialize the number of days to 0	
	--	
	set @NumberOfDays = 0

	--
	-- validate the @EventID. It cannot be null.
	--
	if (@EventID is null)
	begin
		raiserror
		(
			N'@EventID parameter cannot be null.'
			, 10 -- severity
			, 1 -- state
		)
	end
	
	--
	-- validate the @EventTypeName. It cannot be null or empty.
	--
	if (@EventTypeName is null or LEN(@EventTypeName) = 0)
	begin
		raiserror
		(
			N'@EventTypeName parameter cannot be null or empty.'
			, 10 -- severity
			, 1 -- state
		)
	end

	--
	-- get the event type id from the event type name.
	--
	exec @EventTypeID = [dbo].[saveEventType_sp]
						@EventTypeName = @EventTypeName

	--
	-- get the max date modified for the event
	--
	select @MaxDateModified = max(DateModified)
	from [dbo].[EventHits]
	where EventID = @EventID
	and EventTypeID = @EventTypeID

	--
	-- get the number of days to pull data for. Default is 90 days.
	--
	if (@MaxDateModified is null)
	begin
		select @NumberOfDays = 90
	end
	else
	begin
		select @NumberOfDays = datediff(
				dd 
				, @MaxDateModified
				, cast(convert(varchar, getdate(), 101) as smalldatetime)
				)
	end

	--
	-- truncate the stage event hits table
	--
	truncate table [dbo].[StageEventHits]

	--
	-- return the number of days
	--
	select @NumberOfDays
end
go

set ansi_nulls off
go
set quoted_identifier off
go
